#!/usr/bin/env python
# -*- encoding: utf-8 -*-
from six import itervalues
import pymysql,traceback

class MYSQL():
    def __init__(self,host,port,user,pwd,db):
        self.host = host
        self.port = port
        self.user = user
        self.pwd = pwd
        self.db = db

    def __GetConnect(self):
        if not self.db:
            raise(NameError,u"没有设置数据库信息")
        self.conn = pymysql.connect(host=self.host, port=self.port, user=self.user, password=self.pwd, db=self.db,charset='utf8', cursorclass=pymysql.cursors.DictCursor)
        cur = self.conn.cursor()
        if not cur:
            raise(NameError,u"连接数据库失败")
        else:
            return cur

    def ExecQuery(self,sql):
        cur = self.__GetConnect()
        cur.execute(sql)

        resList = []
        res = cur.fetchall()
        resList.append(res)

        while cur.nextset():
            res = cur.fetchall()
            resList.append(res)
        #查询完毕后必须关闭连接
        self.conn.close()
        return resList

    def ExecNonQuery(self,sql):
        cur = self.__GetConnect()
        cur.execute(sql)
        self.conn.commit()
        self.conn.close()

    def ExecProc(self,proc_name,list_proc_args):
        cur = self.__GetConnect()
        cur.callproc(proc_name,list_proc_args)
        resList = []
        res = cur.fetchall()
        resList.append(res)

        while cur.nextset():
            res = cur.fetchall()
            resList.append(res)
        resList.pop(-1)

        self.conn.close()
        return resList

    #插入数据到数据库   
    def insert(self,tablename=None,**values):
        cur = self.__GetConnect()
        if values:
            _keys = ",".join('%s'%k for k in values)
            _values = ",".join(['%s',]*len(values))
            sql_query = "insert into %s (%s) values (%s)" % (tablename,_keys,_values)
        else:
            sql_query = "replace into %s default values" % tablename
        try:
            if values:
                cur.execute(sql_query,list(itervalues(values)))
            else:       
                cur.execute(sql_query)
            self.conn.commit()
            self.conn.close()
            return True
        except:
            traceback.print_exc()
            return False

    def select(self,tablename,query_fields,constrains):
        cur  = self.__GetConnect()
        if not query_fields:
            _query_fields = '*'
        else:
            _query_fields = ','.join(query_fields)
        if not constrains:
            _constrains = '1=1'
        else:
            _constrains = ' and '.join("%s='%s'"%(k,v) for (k,v) in constrains.items())

        sql_query = "select %s from %s where %s"%(_query_fields,tablename,_constrains)
        try:
            cur.execute(sql_query)
        except:
            print("An Error Occured")
            return None

        resList = []
        res = cur.fetchall()
        resList.append(res)
        while cur.nextset():
            res = cur.fetchall()
            resList.append(res)
        # 查询完毕后必须关闭连接
        self.conn.close()
        return resList

    def test(self):
        print 43

if __name__=='__main__':
    s = MYSQL('10.116.38.2',3306,'thkj','thqx.0803','jxfw_basemete_data')
    s.select('1',['3','4'],{'1':2})
